package utils;

import constant.ExceptionConstant;
import pojo.Admin;
import pojo.StudentCondition;
import properties.MysqlProperties;
import pojo.Student;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.StringJoiner;

public class JDBCUtil {
    private static final Connection connection = getConnection();

    /**
     * 获取数据库连接对象
     *
     * @return
     */
    private static Connection getConnection() {
        try {
            Class.forName(MysqlProperties.driver);
            return DriverManager.getConnection(MysqlProperties.url, MysqlProperties.username, MysqlProperties.password);
        } catch (Exception e) {
            throw new RuntimeException(ExceptionConstant.DATABASE_DRIVER_LOAD_EXCEPTION);
        }
    }

    /**
     * 插入学生信息
     *
     * @param stu
     * @return
     */
    public static int insert(Student stu) throws SQLException {
        //构造SQL语句
        String sql = "insert into student(sno, name, gender, age, id_number, address, phone, depart, major, now_class) values ("
                + change(stu.getSno()) + ","
                + change(stu.getName()) + ","
                + change(stu.getGender()) + ","
                + change(stu.getAge()) + ","
                + change(stu.getId_number()) + ","
                + change(stu.getAddress()) + ","
                + change(stu.getPhone()) + ","
                + change(stu.getDepart()) + ","
                + change(stu.getMajor()) + ","
                + change(stu.getNow_class()) + ")";
        //执行SQL语句
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);
        statement.close();
        //返回结果
        return result;
    }

    /**
     * 插入管理员信息
     *
     * @param admin
     * @return
     * @throws SQLException
     */
    public static int insert(Admin admin) throws SQLException {
        //构造SQL语句
        String sql = "insert into admin (username, password, create_time, update_time) VALUES ("
                + change(admin.getUsername()) + ","
                + change(encryptionPassword(admin.getPassword())) + ","
                + change(TimeUtil.localDateTimeToMysqlFormat(LocalDateTime.now())) + ","
                + change(TimeUtil.localDateTimeToMysqlFormat(LocalDateTime.now())) + ")";
        //执行SQL语句
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);
        statement.close();
        //返回结果
        return result;
    }

    /**
     * 根据学号删除学生信息
     *
     * @param sno
     * @return
     * @throws SQLException
     */
    public static int delete(String sno) throws SQLException {
        //构造SQL语句
        String sql = "delete from student where sno = " + change(sno);
        //执行SQL语句
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);
        statement.close();
        //返回结果
        return result;
    }

    /**
     * 修改学生信息
     *
     * @return
     */
    public static int update(Student stu) throws SQLException {
        //构造SQL语句
        String sql = "update student set "
                + "name = " + change(stu.getName()) + ","
                + "gender = " + change(stu.getGender()) + ","
                + "age = " + change(stu.getAge()) + ","
                + "id_number = " + change(stu.getId_number()) + ","
                + "address = " + change(stu.getAddress()) + ","
                + "phone = " + change(stu.getPhone()) + ","
                + "depart = " + change(stu.getDepart()) + ","
                + "major = " + change(stu.getMajor()) + ","
                + "now_class = " + change(stu.getNow_class())
                + " where sno = " + change(stu.getSno());
        //执行SQL语句
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);
        statement.close();
        //返回结果
        return result;
    }

    /**
     * 修改管理员信息
     *
     * @param admin
     * @return
     */
    public static int update(Admin admin) throws SQLException {
        //构造SQL语句
        String sql = "update admin set "
                + "password = " + change(encryptionPassword(admin.getPassword())) + ","
                + "update_time = " + change(TimeUtil.localDateTimeToMysqlFormat(admin.getUpdate_time()))
                + " where username = " + change(admin.getUsername());
        //执行SQL语句
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);
        statement.close();
        //返回结果
        return result;
    }

    /**
     * 查询所有学生信息
     *
     * @return
     */
    public static List<Student> selectSudent() throws SQLException {
        //构造SQL
        String sql = "select * from student";
        //执行SQL得到ResultSet
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        //把ResultSet转换成List集合
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {
            Student student = new Student();
            CopyUtil.copyFromResultSet(student, resultSet);
            list.add(student);
        }
        statement.close();
        //返回结果集合
        return list;
    }

    /**
     * 条件查询学生信息
     *
     * @param studentCondition
     * @return
     */
    public static List<Student> selectStudent(StudentCondition studentCondition) throws SQLException {
        final Integer MIN_AGE = 0;
        final Integer MAX_AGE = Integer.MAX_VALUE;

        //构造SQL语句
        StringBuilder sql = new StringBuilder("select * from student where ");

        // 处理sno字段
        if (studentCondition.getSno() == null) {
            sql.append("(sno like '%' or sno is null)");
        } else {
            sql.append("sno like ").append(change("%" + studentCondition.getSno() + "%"));
        }

        // 处理name字段
        sql.append(" and ");
        if (studentCondition.getName() == null) {
            sql.append("(name like '%' or name is null)");
        } else {
            sql.append("name like ").append(change("%" + studentCondition.getName() + "%"));
        }

        // 处理gender字段
        sql.append(" and gender in (");
        if (studentCondition.getGender() == null) {
            sql.append("1, 2");
        } else {
            sql.append(change(studentCondition.getGender()));
        }
        sql.append(")");

        // 处理age字段
        sql.append(" and age between ")
                .append(change(studentCondition.getBeginAge() == null ? MIN_AGE : studentCondition.getBeginAge()))
                .append(" and ")
                .append(change(studentCondition.getEndAge() == null ? MAX_AGE : studentCondition.getEndAge()));

        // 处理id_number字段
        sql.append(" and ");
        if (studentCondition.getId_number() == null) {
            sql.append("(id_number like '%' or id_number is null)");
        } else {
            sql.append("id_number like ").append(change("%" + studentCondition.getId_number() + "%"));
        }

        // 处理address字段
        sql.append(" and ");
        if (studentCondition.getAddress() == null) {
            sql.append("(address like '%' or address is null)");
        } else {
            sql.append("address like ").append(change("%" + studentCondition.getAddress() + "%"));
        }

        // 处理phone字段
        sql.append(" and ");
        if (studentCondition.getPhone() == null) {
            sql.append("(phone like '%' or phone is null)");
        } else {
            sql.append("phone like ").append(change("%" + studentCondition.getPhone() + "%"));
        }

        // 处理depart字段
        sql.append(" and ");
        if (studentCondition.getDepart() == null) {
            sql.append("(depart like '%' or depart is null)");
        } else {
            sql.append("depart like ").append(change("%" + studentCondition.getDepart() + "%"));
        }

        // 处理major字段
        sql.append(" and ");
        if (studentCondition.getMajor() == null) {
            sql.append("(major like '%' or major is null)");
        } else {
            sql.append("major like ").append(change("%" + studentCondition.getMajor() + "%"));
        }

        // 处理now_class字段
        sql.append(" and ");
        if (studentCondition.getNow_class() == null) {
            sql.append("(now_class like '%' or now_class is null)");
        } else {
            sql.append("now_class like ").append(change("%" + studentCondition.getNow_class() + "%"));
        }

        //执行SQL得到ResultSet
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql.toString());

        //把ResultSet转换成List集合
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {
            Student student = new Student();
            CopyUtil.copyFromResultSet(student, resultSet);
            list.add(student);
        }
        statement.close();

        //返回结果集合
        return list;
    }

    /**
     * 查询所有管理员信息
     *
     * @return
     */
    public static List<Admin> selectAdmin() throws SQLException {
        //构造SQL
        String sql = "select * from admin";
        //执行SQL得到ResultSet
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        //把ResultSet转换成List集合
        List<Admin> list = new ArrayList<>();
        while (resultSet.next()) {
            Admin admin = new Admin();
            CopyUtil.copyFromResultSet(admin, resultSet);
            list.add(admin);
        }
        statement.close();
        //返回结果集合
        return list;
    }

    /**
     * 辅助SQL，动态加引号
     *
     * @param object
     * @param <T>
     * @return
     */
    private static <T> String change(T object) {
        if (object == null) {
            return null;
        }
        if (object instanceof String || object instanceof LocalDateTime) {
            return "'" + object + "'";
        } else {
            return object.toString();
        }
    }

    /**
     * 加密密码，保证数据安全
     *
     * @param password
     * @return
     */
    public static String encryptionPassword(String password) {
        byte[] bytes = password.getBytes();
        char[] letters = {'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '_'};
        final int LETTER_LENGTH = letters.length;
        for (int i = 0; i < bytes.length; i++) {
            bytes[i] ^= bytes[bytes.length - i - 1];
            bytes[i] %= LETTER_LENGTH;
            bytes[i] = (byte) ((byte) (bytes[i] + 3) < LETTER_LENGTH ? (bytes[i] + 3) : (bytes[i] + 3 - LETTER_LENGTH));
            bytes[i] = (byte) letters[Math.abs(bytes[i])];
        }
        return new String(bytes);
    }
}
